package cn.waka.sys.admin.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Set;

import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Component;

import cn.waka.sys.admin.object.Page;
import cn.waka.sys.admin.object.Resources;

@Component
public class ResourceDao extends BaseDao {
	
	//计算集数
	public int getResourceTotal(String field,String value){	
		String where = "";
		if(value!=""){
			where = " and "+field+"="+value;
		}
		String sql = "SELECT count(1) as count FROM s_resource WHERE is_del=0 and status=1 "+where;
		int count= this.queryForObject(sql, Integer.class);
		return count;
	}
	//影片管理总列表
	public Map<String,Object> getResourceList(int start,int size){
		String sql="SELECT r.id,r.title,r.img,r.describe,d.`value` AS source_name,r.region,r.language,r.year,r.type,'status',is_selected FROM s_resource r LEFT JOIN w_dic d ON r.source=d.id LIMIT ?,?";
		List<Map<String,Object>> list = this.query(sql, new RowMapper<Map<String,Object>>() {
			@Override
			public Map<String,Object> mapRow(ResultSet rs, int rowNum) throws SQLException {
				Map<String,Object> row=new HashMap<String,Object>();
				row.put("id", rs.getInt(1));
				row.put("title", rs.getString(2));
				row.put("img", rs.getString(3));
				row.put("describe", rs.getString(4));
				row.put("source", rs.getString(5));
				row.put("region", dicValue(rs.getInt(6)));
				row.put("language", dicValue(rs.getInt(7)));
				row.put("year", rs.getString(8));
				row.put("type", dicValue(rs.getInt(9)));
				row.put("status", dicValue(rs.getInt(10)));
				row.put("is_selected", dicValue(rs.getInt(11)));
				return row;
			}
		}, new Object[]{start,size});
		
		return list.size()>0?list.get(0):null;
	}
	
	//课程影片列表
	public List<Object> getSchoolResources(Page p){
		int page=p.getPage();
		int start=(page-1)*p.getSize();
		String sql="SELECT id,img,title FROM s_resource WHERE `is_del`=0 AND school !=0 LIMIT ?,?";
		List<Object> list=this.query(sql, new RowMapper<Object>(){
			@Override
			public Object mapRow(ResultSet rs, int rowNum) throws SQLException {
				Map<String,Object> row=new HashMap<String,Object>();
				row.put("id", rs.getInt(1));
				row.put("img", rs.getString(2));
				row.put("title", rs.getString(3));
				return row;
			}
		},start,p.getSize());
		return list;
	}
	//查询总数
	public int findCount(Page p){
		String sql="select count(1) from s_resource WHERE `is_del`=0 AND school !=0";
		return this.queryForObject(sql,Integer.class);
	}
	//删除一个
	public int deleteResource(int id) {
		String sql = "DELETE FROM `s_resource` WHERE id =?";
		int rs = this.update( sql, id);
		return rs;
	}
	
	//根据ID 视频集数
	public int getVideoTotal(int resource_id){	
		String sql = "SELECT count(1) as count FROM s_resource_data WHERE resource_id=?";
		int count= this.queryForObject(sql, Integer.class,resource_id);
		return count;
	}
	
	public String dicValue(int id){
		String sql="SELECT `value` FROM `w_dic` WHERE id=? LIMIT 0,1";
		List<String> list = this.query(sql, new RowMapper<String>() {
			@Override
			public String mapRow(ResultSet rs, int rowNum) throws SQLException {
				String value = rs.getString(1);
				return value;
			}
		}, new Object[]{id});
		
		return list.size()>0?list.get(0):"";
	}
	
	public List<Map<String,Object>> findList(Page p,Map<String,String> param){
		int page=p.getPage();
		int start=(page-1)*p.getSize();
		
		String sql="SELECT id,title,img,`describe`,source,region,`language`,`year`,`type`,`status`,is_selected,c_time,u_time,program FROM s_resource where is_del=0 ";
		Object[] dbparams = null;
		int i=0;
		if(param != null&&param.size()>0){
			Set<String> pks= param.keySet();
			dbparams = new Object[2 + param.size()];
			for(String pa : pks){
				sql+=" and "+pa+" like ?";
				dbparams[i]=param.get(pa);
				i++;
			}
			
			dbparams[param.size()]=start;
			dbparams[param.size()+1]=p.getSize();
		}else{
			dbparams = new Object[2];
			dbparams[0]=start;
			dbparams[1]=p.getSize();
		}
		sql+=" order by id desc limit ?,?";
		List<Map<String,Object>> list = this.query(sql, new RowMapper<Map<String,Object>>() {
			@Override
			public Map<String,Object> mapRow(ResultSet rs, int rowNum) throws SQLException {
				Map<String,Object> row=new HashMap<String,Object>();
				row.put("id", rs.getInt(1));
				row.put("title", rs.getString(2));
				row.put("img", rs.getString(3));
				row.put("describe", rs.getString(4));
				row.put("source", rs.getInt(5));
				row.put("region", rs.getInt(6));
				row.put("language", rs.getInt(7));
				row.put("year", rs.getString(8));
				row.put("type", rs.getInt(9));
				row.put("status", rs.getInt(10));
				row.put("is_selected", rs.getInt(11));
				row.put("c_time", rs.getString(12));
				row.put("u_time", rs.getString(13));
				return row;
			}
		}, dbparams);
		return list;
	}
	
	public int findCount(Page p,Map<String,String> param){
		String sql="select count(1) FROM s_resource where is_del=0 ";
		String sql_2= "SELECT COUNT(1) FROM s_resource WHERE school>0";
		Object[] dbparams = null;
		if (param!=null&&param.size() > 0) {
			Set<String> pks = param.keySet();
			dbparams = new Object[param.size()];
			int i = 0;
			for (String pa : pks) {
				sql += " and " + pa + " like ?";
				dbparams[i] = param.get(pa);
				i++;
			}
		}
		int c=0;
		if (param!=null&&param.size() > 0) {
			c=this.queryForObject(sql,Integer.class,dbparams);
		}else{
			if(p.getOther()==1){
				c=this.queryForObject(sql_2,Integer.class);
			}else {
				c=this.queryForObject(sql,Integer.class);
			}
		}
		return c;
	}
	
	public int getResourceNum(int id){
		String sql="select count(1) FROM s_resource_data where is_del=0 and resource_id=?";
		int c=this.queryForObject(sql,Integer.class,id);
		return c;
	}
	
	//是否精选
	public int updateSelected(int id,int is_selected) {
		String usql = "UPDATE s_resource SET is_selected =? WHERE id=?";
		int rs = this.update(usql, is_selected,id);
		return rs;
	}
	
	public Resources findById(int id){
		String sql="SELECT id,title,img,`describe`,source,region,`language`,`year`,`type`,`status`,is_selected,subtitle,channel,age,school,program,ip_name,ip_img FROM s_resource where id=?";
		List<Resources> list=this.query(sql, new RowMapper<Resources>(){
			@Override
			public Resources mapRow(ResultSet rs, int rowNum) throws SQLException {
				Resources row=new Resources();
				row.setId(rs.getInt(1));
				row.setTitle(rs.getString(2));
				row.setImg( rs.getString(3));
				row.setDescribe( rs.getString(4));
				row.setSource( rs.getInt(5));
				row.setRegion( rs.getInt(6));
				row.setLanguage( rs.getInt(7));
				row.setYear( rs.getInt(8));
				row.setType( rs.getInt(9));
				row.setStatus( rs.getBoolean(10));
				row.setSelected( rs.getBoolean(11));
				row.setSubtitle( rs.getString(12));
				row.setChannel( rs.getInt(13));
				row.setAge( rs.getInt(14));
				row.setSchool( rs.getInt(15));
				row.setProgram( rs.getInt(16));
				row.setIpname(rs.getString(17));
				row.setIpimg(rs.getString(18));
				return row;
			}
		},id);
		return list.size()>0?list.get(0):null;
	}
	
	public int insert(final Resources r) {
		KeyHolder holder = new GeneratedKeyHolder();
		this.update(new PreparedStatementCreator() {

			@Override
			public PreparedStatement createPreparedStatement(Connection con) throws SQLException {
				String sql = "insert into s_resource(title,subtitle,source,`language`,region,`year`,age,`type`,is_selected,school,channel,program,`describe`,img,u_time,ip_name,ip_img)values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,now(),?,?)";
				PreparedStatement ps = con.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
				ps.setString(1, r.getTitle());
				ps.setString(2, r.getSubtitle());
				ps.setInt(3, r.getSource());
				ps.setInt(4, r.getLanguage());
				ps.setInt(5, r.getRegion());
				ps.setInt(6, r.getYear());
				ps.setInt(7, r.getAge());
				ps.setInt(8, r.getType());
				ps.setBoolean(9, r.isSelected());
				ps.setInt(10, r.getSchool());
				ps.setInt(11, r.getChannel());
				ps.setInt(12, r.getProgram());
				ps.setString(13, r.getDescribe());
				ps.setString(14, r.getImg());
				ps.setString(15, r.getIpname());
				ps.setString(16, r.getIpimg());
				
				return ps;
			}
		}, holder);
		int id = holder.getKey().intValue();
		return id;
	}
	
	public int update(Resources r) {
		int rs = this.update("update s_resource set title=?,subtitle=?,source=?,`language`=?,region=?,`year`=?,age=?,`type`=?,is_selected=?,school=?,channel=?,program=?,`describe`=?,img=?,u_time=now(),ip_name=?,ip_img=? where id=?",
			r.getTitle(),
			r.getSubtitle(),
			r.getSource(),
			r.getLanguage(),
			r.getRegion(),
			r.getYear(),
			r.getAge(),
			r.getType(),
			r.isSelected(),
			r.getSchool(),
			r.getChannel(),
			r.getProgram(),
			r.getDescribe(),
			r.getImg(),
			r.getIpname(),
			r.getIpimg(),
			r.getId());
		return rs;
	}
	
	public List<Map<String,Object>> resourcePlayLogs(Page p,Map<String,String> param){
		int page=p.getPage();
		int start=(page-1)*p.getSize();
		
		String sql="SELECT login , resource_id , title ,f.`img`, logId , f.`uid` ,play_status ,play_type,create_time,uname FROM (SELECT resource_id , title ,img , logId , uid ,play_status ,play_type,create_time FROM (SELECT a.`id` logId , play_id, play_type,a.`play_status` , a.`play_time` , video_id , box_id ,uid , create_time FROM `s_resource_play_log` a JOIN `s_resource_play` b ON a.`play_id` = b.`id` ) c JOIN `s_resource_data` d ON c.`video_id` = d.`id` ORDER BY create_time DESC) f  JOIN `s_user` e ON e.`uid` = f.`uid` WHERE login IS NOT NULL ";
		Object[] dbparams = null;
		int i=0;
		if(param != null&&param.size()>0){
			Set<String> pks= param.keySet();
			dbparams = new Object[2 + param.size()];
			for(String pa : pks){
				sql+=" and "+pa+" like ?";
				dbparams[i]=param.get(pa);
				i++;
			}
			
			dbparams[param.size()]=start;
			dbparams[param.size()+1]=p.getSize();
		}else{
			dbparams = new Object[2];
			dbparams[0]=start;
			dbparams[1]=p.getSize();
		}
		sql+=" limit ?,?";
		List<Map<String,Object>> list = this.query(sql, new RowMapper<Map<String,Object>>() {
			@Override
			public Map<String,Object> mapRow(ResultSet rs, int rowNum) throws SQLException {
				Map<String,Object> row=new HashMap<String,Object>();
				row.put("login", rs.getString(1));
				row.put("resource_id", rs.getInt(2));
				row.put("title", rs.getString(3));
				row.put("img", rs.getString(4));
				row.put("logId", rs.getInt(5));
				row.put("uid", rs.getInt(6));
				row.put("play_status", rs.getInt(7));
				row.put("play_type", rs.getInt(8));
				row.put("create_time", rs.getTimestamp(9));
				row.put("uname", rs.getString(10));
				return row;
			}
		}, dbparams);
		return list;
	}
	
	public int playLogCount(Page p,Map<String,String> param){
		String sql="select count(1) FROM (SELECT resource_id , title ,img , logId , uid ,play_status ,play_type,create_time FROM (SELECT a.`id` logId , play_id, play_type,a.`play_status` , a.`play_time` , video_id , box_id ,uid , create_time FROM `s_resource_play_log` a JOIN `s_resource_play` b ON a.`play_id` = b.`id` ) c JOIN `s_resource_data` d ON c.`video_id` = d.`id` ORDER BY create_time DESC) f  JOIN `s_user` e ON e.`uid` = f.`uid` WHERE login IS NOT NULL ";
		Set<String> pks= param.keySet();
		Object[] dbparams=new Object[param.size()];
		int i=0;
		if(param.size()>0){
			for(String pa : pks){
				sql+=" and "+pa+" like ?";
				dbparams[i]=param.get(pa);
				i++;
			}
		}
		int c=0;
		if(param.size()>0){
			c=this.queryForObject(sql,Integer.class,dbparams);
		}else{
			c=this.queryForObject(sql,Integer.class);
		}
		return c;
	}

	
	public int del(int id) {
		int r = this.update("update s_resource set is_del=1 where id=?",id);
		return r;
	}
	
	public int delDataByResource(int resource_id) {
		int r = this.update("update s_resource_data set is_del=1 where resource_id=?",resource_id);
		return r;
	}
	
	public int updateStatus(int id,int status) {
		int r = this.update("update s_resource set status=? where id=?",status,id);
		return r;
	}
	
	public int delData(int id) {
		int r = this.update("update s_resource_data set is_del=1 where id=?",id);
		return r;
	}
}
